package de.hsos.bose.skillshare.model.core;
/**
 * 
 * @author Florian Evers
 *
 */
public final class Queries {

	public static String QMitarbeiter = 	"SELECT MITA.MITA_ID,MITA.MITA_VORNAME,MITA.MITA_BERUF,MITA.MITA_ANSCHRIFT,MITA.MITA_ZIPCODE,MITA.MITA_CITY,MITA.MITA_NAME,MITAROL.MITAROL_ROL_ID " +
											"FROM MITARBEITER MITA " +
											"JOIN MITARBEITER_ROLLEN MITAROL " +
												"ON(MITA.MITA_ID = MITAROL.MITAROL_MITA_ID) " +
											"WHERE MITA.MITA_ID = ?";

	public static String QValidateLogin = 	"SELECT LOGIN_ID " +
											"FROM MITARBEITER_LOGIN LOGIN " +
											"WHERE LOGIN.LOGIN_USER = ? AND LOGIN.LOGIN_PASSWORD = ?";

	public static String QValidateUsername = "SELECT LOGIN_ID " +
			                                "FROM MITARBEITER_LOGIN LOGIN " +
			                                "WHERE LOGIN.LOGIN_USER = ? ";
	
	public static String QUser = 	        "SELECT MITA.MITA_ID, MITA.MITA_VORNAME, MITA.MITA_NAME, MITA.MITA_BERUF, LOGIN.LOGIN_USER, LOGIN.LOGIN_PASSWORD, MITAROL.MITAROL_ROL_ID " +
			                                "FROM MITARBEITER MITA " +
			                                "LEFT OUTER JOIN MITARBEITER_LOGIN LOGIN " +
			                                "ON(MITA.MITA_ID = LOGIN.LOGIN_ID) " +
			                                "LEFT OUTER JOIN MITARBEITER_ROLLEN MITAROL " +
			                                "ON(MITA.MITA_ID = MITAROL.MITAROL_MITA_ID) " +
			                                "ORDER BY MITA.MITA_ID";
	
	public static String QAssignedUsers = 	"SELECT MITA.MITA_ID, MITA.MITA_VORNAME, MITA.MITA_NAME, MITA.MITA_BERUF, MITAROL.MITAROL_ROL_ID " +
									        "FROM MITARBEITER MITA " +
									        "JOIN MITARBEITER_ROLLEN MITAROL " +
									        	"ON(MITA.MITA_ID = MITAROL.MITAROL_MITA_ID) " +
									        "JOIN MITARBEITER_ZUORDNUNG MITAZU " +
									        	"ON(MITAZU.MITZU_MITA_ID = MITA.MITA_ID) " +
									        "WHERE MITAZU.MITZU_CHEF_ID = ?" +
									        "ORDER BY MITA.MITA_ID";
	
	public static String QAssignUsers = 	"SELECT MITA.MITA_ID, MITA.MITA_VORNAME, MITA.MITA_NAME, MITA.MITA_BERUF, MITAROL.MITAROL_ROL_ID " +
	        								"FROM MITARBEITER MITA " +
	        								"JOIN MITARBEITER_ROLLEN MITAROL " +
	        									"ON(MITA.MITA_ID = MITAROL.MITAROL_MITA_ID) " +
	        								"LEFT OUTER JOIN ( " +
	        										"SELECT MITA.MITA_ID AS MITA_ID " +
	        										"FROM MITARBEITER MITA " +
	        										"JOIN MITARBEITER_ROLLEN MITAROL " +
	        											"ON(MITA.MITA_ID = MITAROL.MITAROL_MITA_ID) " +
	        										"JOIN MITARBEITER_ZUORDNUNG MITAZU " +
	        											"ON(MITAZU.MITZU_MITA_ID = MITA.MITA_ID) " +
	        										"WHERE MITAZU.MITZU_CHEF_ID = ? " +
	        									") AS ADMINZU " +
	        									"ON(ADMINZU.MITA_ID = MITA.MITA_ID) " +
	        								"WHERE MITAROL.MITAROL_ROL_ID < ? " +
	        									"AND ADMINZU.MITA_ID IS NULL " +
	        								"ORDER BY MITA.MITA_ID";
	
	public static String QSkillGruppen =	"SELECT skg.skg_id, skg.skg_name, skg.skg_description " +
											"FROM skill_gruppen skg " +
											"ORDER BY skg.skg_id"; 	
	
	public static String QSkillDetails = 	"SELECT skd.skd_id, skd.skd_name, skd.skd_description, skd.skd_skg_id " +
											"FROM skill_detail skd " +
											"WHERE skd.skd_skg_id = ? " +
											"ORDER BY skd.skd_id";
	
	public static String QAllSkillDetails = "SELECT skd.skd_id, skd.skd_name, skd.skd_description, skd.skd_skg_id " +
			                                "FROM skill_detail skd " +
			                                "ORDER BY skd.skd_id";
	
	public static String QSkills =			"SELECT skm.skm_id, skm.skm_mita_id, skm.skm_skd_id, skd.skd_skg_id, skg.skg_name, skd.skd_name, skm.skm_bewertung " +
											"FROM mitarbeiter_skill skm " +
											"JOIN mitarbeiter mita " +
												"ON(mita.mita_id = skm.skm_mita_id) " +
											"JOIN skill_detail skd " +
												"ON(skm.skm_skd_id = skd.skd_id) " +
											"JOIN skill_gruppen skg " +
												"ON(skd.skd_skg_id = skg.skg_id) " +
											"WHERE skm.skm_mita_id = ?";
	
	public static String QSkillOverview =   "SELECT skm.skm_id, skm.skm_mita_id, mita.mita_vorname, mita.mita_name, skd.skd_skg_id, skg.skg_name, skm.skm_skd_id, skd.skd_name, skm.skm_bewertung " +
			                                "FROM mitarbeiter_skill skm " +
			                                "JOIN mitarbeiter mita " +
			                                	"ON(mita.mita_id = skm.skm_mita_id) " +
			                                "JOIN skill_detail skd " +
			                                	"ON(skm.skm_skd_id = skd.skd_id) " +
			                                "JOIN skill_gruppen skg " +
			                                	"ON(skd.skd_skg_id = skg.skg_id) " +
			                                "JOIN (" +
			                                		"SELECT MITA.MITA_ID AS MITA_ID " +
			                                		"FROM MITARBEITER MITA " +
			                                		"JOIN MITARBEITER_ZUORDNUNG MITAZU " +
			                                			"ON(MITAZU.MITZU_MITA_ID = MITA.MITA_ID) " +
			                                		"WHERE MITAZU.MITZU_CHEF_ID = ? " +
			                                	") assign " +
			                                	"ON(skm.skm_mita_id = assign.mita_id) ";
	
	public static String QRolls =			"SELECT ROLL.ROL_ID, ROLL.ROL_NAME FROM ROLLEN ROLL";
	
	public static String ISkillGruppen =	"INSERT INTO skill_gruppen (skg_name) " +
											"VALUES ( ? )";
	
	public static String ISkillDetail =		"INSERT INTO skill_detail (skd_name, skd_skg_id) " +
											"VALUES ( ? , ? )";
	
	public static String ISkill =			"INSERT INTO mitarbeiter_skill (skm_mita_id, skm_skd_id, skm_bewertung) " +
											"VALUES ( ? , ? , ? )";
	
	public static String IUserLogin =		"INSERT INTO mitarbeiter_login (login_id, login_user, login_password) " +
											"VALUES ( ? , ? , ? )";
	
	public static String IUserRoll =		"INSERT INTO mitarbeiter_rollen (mitarol_mita_id, mitarol_rol_id) " +
											"VALUES ( ? , ? )";
	
	public static String IAssign =			"INSERT INTO mitarbeiter_zuordnung (mitzu_chef_id, mitzu_mita_id) " +
											"VALUES ( ? , ? )";
	
	public static String DUserLogin =		"DELETE FROM mitarbeiter_rollen " +
											"WHERE mitarol_mita_id = ?";

	public static String DUserRoll =		"DELETE FROM mitarbeiter_login " +
											"WHERE login_id = ?";
	
	public static String DAssignment =		"DELETE FROM mitarbeiter_zuordnung " +
											"WHERE mitzu_chef_id = ?" +
											"AND mitzu_mita_id = ?";
	
	public static String DSkillGroup =		"DELETE FROM skill_gruppen " +
                                            "WHERE skg_id = ? ";	
	
	public static String DSkillDetail =		"DELETE FROM skill_detail " +
                                            "WHERE skd_id = ? ";
	
	public static String DSkill =			"DELETE FROM mitarbeiter_skill " +
                                            "WHERE skm_id = ? ";
											
	public static String USkillGroup =		"UPDATE skill_gruppen " +
			                                "SET skg_name = ? , skg_description = ? " +
	                                        "WHERE skg_id = ? ";	
	
	public static String USkillDetail =		"UPDATE skill_detail " +
                                            "SET skd_name = ? , skd_description = ? " +
                                            "WHERE skd_id = ? ";	
	
	public static String USkill =			"UPDATE mitarbeiter_skill " +
                                            "SET skm_bewertung = ? " +
                                            "WHERE skm_id = ? ";
	
	/**
	 * Konstruktor von der Klasse Queries.
	 */
	private Queries() {

	}

}
